package com.synjones.zeuscloudserver.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
    public List<String> getSno(String path)throws IOException{
        List<String> result = new ArrayList<>();
        FileInputStream fis = new FileInputStream(path);
        Workbook workbook = null;
        //判断excel的两种格式xls,xlsx
        if(path.toLowerCase().endsWith("xlsx")){
            workbook = new XSSFWorkbook(fis);
        }else if(path.toLowerCase().endsWith("xls")){
            workbook = new HSSFWorkbook(fis);
        }
        //得到sheet的总数
        int numberOfSheets = workbook.getNumberOfSheets();
        //循环每一个sheet
        for(int i=0; i < numberOfSheets; i++){
            //得到第i个sheet
            Sheet sheet = workbook.getSheetAt(i);
            //得到行的迭代器
            Iterator<Row> rowIterator = sheet.iterator();
            //循环每一行
            while (rowIterator.hasNext())
            {
                //得到一行对象
                Row row = rowIterator.next();
                //得到列对象
                Iterator<Cell> cellIterator = row.cellIterator();
                //循环每一列
                while (cellIterator.hasNext())
                {
                    //得到单元格对象
                    Cell cell = cellIterator.next();
                    //检查数据类型
                    switch(cell.getCellType()){
                        case Cell.CELL_TYPE_STRING:
                            result.add(cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue()+"   ");
                    }
                } //end of cell iterator
            } //end of rows iterator
        } //end of sheets for loop
        //close file input stream
        fis.close();
        return result;
    }
    public static void main(String[] args) throws IOException {
        ExcelUtil excelUtil = new ExcelUtil();
       List<String> snoList = excelUtil.getSno("D:\\sno.xlsx");
       System.out.println(snoList.toString());
    }
}